﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KTool2.Core;
using KTool2.Database;
using System.Data.OleDb;
using KTool2.Impl;
using System.Data;

namespace KTool2.Utility
{
    /// <summary>
    /// Utility class for program.
    /// </summary>
    class KanjiWordListProcessor
    {
        /// <summary>
        /// Hide default constructor.
        /// </summary>
        protected KanjiWordListProcessor()
        {
            throw new UnauthorizedAccessException();
        }

        /// <summary>
        /// Get list of kanji word list that contains only id, memrate, category.
        /// </summary>
        /// <param name="category">table name or sheet name</param>
        /// <returns>list of kanji word list</returns>
        public static IKanjiWordList GetKanjiWordList(string category)
        {
            if (!DBManagement.IsDBContains(category.Replace('#', '.')))
            {
                throw new Exceptions.DBException(2, category.Replace('#', '.'));
            }

            int id = -1;
            int memRate = -1;
            KanjiWord kWord = null;
            IKanjiWordList kWordList = new KanjiWordList();
            StringBuilder cmdTextBuilder = new StringBuilder();

            using (OleDbConnection connection = DBManagement.OpenedConnection)
            using (OleDbCommand cmd = new OleDbCommand())
            {
                //cmd.Parameters.AddWithValue("@table", category);
                cmdTextBuilder.Clear();
                cmdTextBuilder.Append("SELECT ID, MEMRATE FROM [").Append(category.Replace('#', '.')).Append("]");
                cmd.CommandText = cmdTextBuilder.ToString();
                cmd.Connection = connection;
                using (OleDbDataReader reader = DBManagement.ExcuteReader(cmd))
                //using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        id = int.Parse(reader.GetString(0).Substring(1));
                        memRate = int.Parse(reader.GetString(1).Substring(1));
                        kWord = new KanjiWord(id, memRate, category);
                        kWordList.Add(kWord);
                    }
                }
            }

            GC.WaitForPendingFinalizers();
            GC.Collect();

            return kWordList;
        }

        /// <summary>
        /// Get list of kanji word list that contains only id, memrate, category.
        /// </summary>
        /// <param name="category">table name or sheet name</param>
        /// <param name="memrate">memrate of kanji word</param>
        /// <returns>list of kanji word list</returns>
        public static IKanjiWordList GetKanjiWordList(string category, int memrate)
        {
            if (!DBManagement.IsDBContains(category.Replace('#', '.')))
            {
                throw new Exceptions.DBException(2, category.Replace('#', '.'));
            }

            int id = -1;
            int memRate = -1;
            KanjiWord kWord = null;
            IKanjiWordList kWordList = new KanjiWordList();
            StringBuilder cmdTextBuilder = new StringBuilder();

            using (OleDbConnection connection = DBManagement.OpenedConnection)
            using (OleDbCommand cmd = new OleDbCommand())
            {
                //cmd.Parameters.AddWithValue("@table", category);
                cmdTextBuilder.Clear();
                cmdTextBuilder.Append("SELECT ID, MEMRATE FROM [").Append(category
                    .Replace('#', '.')).Append("] WHERE MEMRATE = '#")
                    .Append(memrate).Append('\'');
                cmd.CommandText = cmdTextBuilder.ToString();
                cmd.Connection = connection;
                using (OleDbDataReader reader = DBManagement.ExcuteReader(cmd))
                //using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        id = int.Parse(reader.GetString(0).Substring(1));
                        memRate = int.Parse(reader.GetString(1).Substring(1));
                        kWord = new KanjiWord(id, memRate, category);
                        kWordList.Add(kWord);
                    }
                }
            }

            GC.WaitForPendingFinalizers();
            GC.Collect();

            return kWordList;
        }

        /// <summary>
        /// Get all kanji words in database/excel file
        /// </summary>
        /// <returns>all kanji words that available in database/excel file</returns>
        public static IKanjiWordList GetAllKanjiWord()
        {
            int id = -1;
            int memRate = -1;
            KanjiWord kWord = null;
            string[] tableList = DBManagement.TableArray;
            IKanjiWordList allKWordList = new KanjiWordList();
            StringBuilder cmdTextBuilder = new StringBuilder();

            using (OleDbConnection connection = DBManagement.OpenedConnection)
            using (OleDbCommand cmd = new OleDbCommand())
            {
                foreach (string table in tableList)
                {
                    //cmd.Parameters.AddWithValue("@table", "[JPN1.3$]");
                    cmdTextBuilder.Clear();
                    cmdTextBuilder.Append("SELECT ID, MEMRATE FROM [").Append(table.Replace('#', '.')).Append("]");
                    cmd.CommandText = cmdTextBuilder.ToString();
                    cmd.Connection = connection;
                    using (OleDbDataReader reader = DBManagement.ExcuteReader(cmd))
                    //using (OleDbDataReader reader = cmd.ExecuteReader())
                    {
                        id = -1;
                        memRate = -1;
                        kWord = null;
                        while (reader.Read())
                        {
                            id = int.Parse(reader.GetString(0).Substring(1));
                            memRate = reader.GetString(1)[1] - '0';
                            kWord = new KanjiWord(id, memRate, table);
                            allKWordList.Add(kWord);
                        }
                    }
                }
            }

            GC.WaitForPendingFinalizers();
            GC.Collect();

            return allKWordList;
        }

        /// <summary>
        /// Get all kanji words in database/excel file
        /// </summary>
        /// <returns>all kanji words that available in database/excel file</returns>
        public static IKanjiWordList GetAllKanjiWord(int memrate)
        {
            int id = -1;
            int memRate = -1;
            KanjiWord kWord = null;
            string[] tableList = DBManagement.TableArray;
            IKanjiWordList allKWordList = new KanjiWordList();
            StringBuilder cmdTextBuilder = new StringBuilder();

            using (OleDbConnection connection = DBManagement.OpenedConnection)
            using (OleDbCommand cmd = new OleDbCommand())
            {
                foreach (string table in tableList)
                {
                    //cmd.Parameters.AddWithValue("@table", "[JPN1.3$]");
                    cmdTextBuilder.Clear();
                    cmdTextBuilder.Append("SELECT ID, MEMRATE FROM [").Append(table.Replace('#', '.'))
                        .Append("] WHERE MEMRATE = '#").Append(memrate).Append('\'');
                    cmd.CommandText = cmdTextBuilder.ToString();
                    cmd.Connection = connection;
                    using (OleDbDataReader reader = DBManagement.ExcuteReader(cmd))
                    //using (OleDbDataReader reader = cmd.ExecuteReader())
                    {
                        id = -1;
                        memRate = -1;
                        kWord = null;
                        while (reader.Read())
                        {
                            id = int.Parse(reader.GetString(0).Substring(1));
                            memRate = reader.GetString(1)[1] - '0';
                            kWord = new KanjiWord(id, memRate, table);
                            allKWordList.Add(kWord);
                        }
                    }
                }
            }

            GC.WaitForPendingFinalizers();
            GC.Collect();

            return allKWordList;
        }

        /////////////////////////////////////////// Moved to Database.DBManagement
        ///// <summary>
        ///// Get list of table in database/excel file.
        ///// </summary>
        ///// <returns>List of table in database/excel file</returns>
        //public static string[] GetTableArray()
        //{
        //    string[] tableArray = null;

        //    using (OleDbConnection connection = DBManagement.OpenedConnection)
        //    using (DataTable dataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
        //    {
        //        tableArray = new String[dataTable.Rows.Count];
        //        for (int i = 0; i < dataTable.Rows.Count; i++)
        //        {
        //            tableArray[i] = dataTable.Rows[i]["TABLE_NAME"].ToString();
        //        }
        //    }

        //    return tableArray;
        //}

        /// <summary>
        /// Searchs database/excel file for a key word
        /// </summary>
        /// <param name="keyWord">key word to be searched</param>
        /// <returns>list of full properties kanji word that each has the key word</returns>
        public static IKanjiWordList Search(string keyWord)
        {
            int id = -1;
            int memRate = -1;
            string kanji = null;
            string hiragana = null;
            string english = null;
            string hanNom = null;
            string viet = null;
            KanjiWord kWord = null;
            string[] tableList = DBManagement.TableArray;
            IKanjiWordList resultList = new KanjiWordList();
            StringBuilder cmdTextBuilder = new StringBuilder();

            using (OleDbConnection connection = DBManagement.OpenedConnection)
            //using (OleDbCommand cmd = new OleDbCommand(
            //    "SELECT ID, KANJI, HIRAGANA, ENGLISH, HANNOM, VIET, MEMRATE FROM @table WHERE KANJI LIKE @keyword OR HIRAGANA LIKE @keyword OR ENGLISH LIKE @keyword OR HANNOM LIKE @keyword OR VIET LIKE @keyword",
            //    connection))
            using (OleDbCommand cmd = new OleDbCommand())
            {
                //valueBuilder.Append("%'").Append(keyWord).Append("%'");
                //cmd.Parameters.AddWithValue("@keyword", valueBuilder);
                foreach (string table in tableList)
                {
                    cmdTextBuilder.Clear();
                    cmdTextBuilder.Append("SELECT ID, KANJI, HIRAGANA, ENGLISH, HANNOM, VIET, MEMRATE FROM [")
                        .Append(table.Replace('#', '.')).Append("] WHERE KANJI LIKE '%").Append(keyWord).Append("%' OR HIRAGANA LIKE '%")
                        .Append(keyWord).Append("%' OR ENGLISH LIKE '%").Append(keyWord).Append("%' OR HANNOM LIKE '%")
                        .Append(keyWord).Append("%' OR VIET LIKE '%").Append(keyWord).Append("%'");
                    cmd.Connection = connection;
                    cmd.CommandText = cmdTextBuilder.ToString();
                    //cmd.CommandText = "SELECT * FROM [JPN1.3$] WHERE KANJI LIKE '%始%'";
                    //cmd.Parameters.AddWithValue("@table", table)
                    using (OleDbDataReader reader = DBManagement.ExcuteReader(cmd))
                    //using (OleDbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            id = int.Parse(reader.GetString(0).Substring(1));
                            kanji = reader.GetString(1).Trim();
                            hiragana = reader.GetString(2).ToLower().Trim();
                            english = reader.GetString(3).ToLower().Trim();
                            hanNom = reader.GetString(4).ToLower().Trim();
                            viet = reader.GetString(5).ToLower().Trim();
                            memRate = reader.GetString(6)[1] - '0';
                            kWord = new KanjiWord(id, memRate, table, kanji, hiragana, english, hanNom, viet);
                            resultList.Add(kWord);
                        }
                    }
                }
            }

            return resultList;
        }
    }
}
